package com.zr.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.zr.dao.DgSpecificDao;
import com.zr.model.Content;
import com.zr.util.GetJDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 获取对应类型知识的值的数据库操作层实现
 * @author Administrator
 *
 */
public class DgSpecificDaoImpl implements DgSpecificDao{

	/**
	 * 获取对应类型知识的值的数据库操作层实例方法
	 */
	GetJDBCUtil gt = new GetJDBCUtil();
	
	@Override
	public JSONObject getDgSpecificById(int tid ,int page, int pagesize) {
		Connection con = gt.getJDBCConnection();
		
		int total = this.getDgSpecificTotalById(tid);
		
		JSONObject jObject = new JSONObject();
		
		jObject.put("total", total);
		JSONArray jArray = new JSONArray();
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT e_content.cid,e_content.cname,e_content.ctitle,e_content.cabstract,e_content.cspecial,e_content.cstate,e_content.ctime,e_content.ccontent,e_content.cnumber ");
		sql.append("FROM e_content ");
		sql.append("INNER JOIN e_type ");
		sql.append("ON e_content.cname=e_type.tname ");
		sql.append("WHERE e_type.tid=? ");
		sql.append("LIMIT ?,? ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			pst.setInt(2, ((page-1)*pagesize));
			pst.setInt(3, pagesize);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next()){
				Content content = new Content();
				content.setCid(rs.getInt("cid"));
				content.setCstate(rs.getString("cstate"));
				content.setCname(rs.getString("cname"));
				content.setCtitle(rs.getString("ctitle"));
				content.setCcontent(rs.getString("ccontent"));
				content.setCtime(rs.getDate("ctime").toString());
				jArray.add(content);
			}
			
			jObject.put("rows", jArray);
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jObject;
	}

	
	public int getDgSpecificTotalById(int tid) {
		Connection con = gt.getJDBCConnection();
		
		int total = 0;
		
		StringBuffer sql = new StringBuffer(""); 
		sql.append("SELECT count(cid) ");
		sql.append("FROM e_content ");
		sql.append("INNER JOIN e_type ");
		sql.append("ON e_content.cname=e_type.tname ");
		sql.append("WHERE e_type.tid=? ");
		
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, tid);
			ResultSet rs = pst.executeQuery();
			
			if(rs.next()){
				total = rs.getInt("count(cid)");
			}
			
			rs.close();
			pst.close();
			con.close();
		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}
	
}
